﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using ZLDC.Common;
using ZLDC.DAL;
using ZLDC.Topo.Model;
using System.Data.SqlClient;
using ZLDC.Common.BaseUser;

namespace ZLDC.Topo.BLL
{
    public class t_s_tmp_allusr : ZLDC.Topo.Model.EyT_s_tmp_allusr
    {
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataTable Gett_s_tmp_allusrList(string strWhere,int currentPage, int pagesize, out int recordcount)
        {
            recordcount = 0;
            
            int startRow = (currentPage - 1) * pagesize + 1;
            string strSql = " select isNull(count(*),0) from ( " +
                            " select (ROW_NUMBER() OVER (ORDER BY u.id desc )) AS Row,id 序号, name 名称, (select name from t_s_dept where id = u.t_id) 部门名称, station 岗位名称, status 岗位状态,t_id, " +
                            "  ''''+identityno as 身份证,tel  手机号码,curtMobile 手机短号,bankacc 银行帐户 from zldc.t_s_tmp_allusr u where dltflg = 0 "+strWhere+" ) as A";
            DataBase db = new DataBase();
            DataSet ds = new DataSet();
            ds = db.SelectDataBase(strSql, "t_s_tmp_usr");
            recordcount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);

            strSql = "";
            strSql += " select A.* from ( " +
                     "  select (ROW_NUMBER() OVER (ORDER BY u.id desc )) AS Row,id 序号, name 名称, (select name from t_s_dept where id = u.t_id) 部门名称, station 岗位名称, status 岗位状态,t_id, " +
                     "  ''''+identityno as 身份证,tel  手机号码,curtMobile 手机短号,bankacc 银行帐户 from zldc.t_s_tmp_allusr u where dltflg = 0 "+strWhere+") as A ";
            strSql += " where A.Row between " + startRow + " and " + currentPage * pagesize + "";
            ds.Clear();
            ds = db.SelectDataBase(strSql, "t_s_tmp_usr");
            return ds.Tables[0];
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public bool Save(int i)
        {
            s_name = CheckString.CheckStr(s_name);
            s_NamePy = CheckString.CheckStr(s_NamePy);
            s_IdentityNo = CheckString.CheckStr(s_IdentityNo);
            s_tel = CheckString.CheckStr(s_tel);
            s_no = CheckString.CheckStr(s_no);
            s_dsc = CheckString.CheckStr(s_dsc);
            s_t_id = CheckString.CheckStr(s_t_id);
            s_r_id = CheckString.CheckStr(s_r_id);
            s_pwd = CheckString.CheckStr(s_pwd);
            s_station = CheckString.CheckStr(s_station);
            s_status = CheckString.CheckStr(s_status);
            s_rzsj = CheckString.CheckStr(s_rzsj);
            s_lzsj = CheckString.CheckStr(s_lzsj);
            s_fh_id_list = CheckString.CheckStr(s_fh_id_list);
            s_dltflg = CheckString.CheckStr(s_dltflg);
            s_curtmobile = CheckString.CheckStr(s_curtmobile);
            s_bankacc = CheckString.CheckStr(s_bankacc);
            s_fgs = CheckString.CheckStr(s_fgs);
            s_old_id = CheckString.CheckStr(s_old_id);
            s_Void_flag = CheckString.CheckStr(s_Void_flag);
            s_Remark = CheckString.CheckStr(s_Remark);
            s_CreateUser = CheckString.CheckStr(s_CreateUser);
            s_CreateTime = CheckString.CheckStr(s_CreateTime);
            s_ModifyUser = CheckString.CheckStr(s_ModifyUser);
            s_ModifyTime = CheckString.CheckStr(s_ModifyTime);
            s_VoidUser = CheckString.CheckStr(s_VoidUser);
            s_VoidTime = CheckString.CheckStr(s_VoidTime);

            string sql; //Create the sql statement
            if (i == 2)
            {
                sql = "update zldc.T_s_tmp_allusr set  name='" + s_name + "',  NamePy='" + s_NamePy + "',  IdentityNo='" + s_IdentityNo + "',  tel='" + s_tel + "',  dsc='" + s_dsc + "',  t_id='" + s_t_id + "',  r_id='" + s_r_id + "',  pwd='" + s_pwd + "',  station='" + s_station + "',  status='" + s_status + "', dltflg='" + s_dltflg + "',  curtmobile='" + s_curtmobile + "',  bankacc='" + s_bankacc + "',  fgs='" + s_fgs + "',  old_id='" + s_old_id + "',  Void_flag='" + s_Void_flag + "',  Remark='" + s_Remark + "',  CreateUser='" + s_CreateUser + "',  CreateTime='" + s_CreateTime + "',  ModifyUser='" + s_ModifyUser + "',  ModifyTime='" + s_ModifyTime + "',  VoidUser='" + s_VoidUser + "',  VoidTime='" + s_VoidTime + "' where Id= '" + s_Id + "'";
            }
            else
            {
                sql = "insert into zldc.T_s_tmp_allusr( name, IdentityNo, tel, dsc,  t_id,  r_id,  pwd,  station,  status,  rzsj, fh_id_list,  dltflg,  curtmobile,  bankacc,  fgs,  old_id,  Void_flag,  Remark,  CreateUser,  CreateTime,  ModifyUser,  ModifyTime,  VoidUser,  VoidTime ) values( '" + s_name + "','" + s_IdentityNo + "',  '" + s_tel + "', '" + s_dsc + "',  '" + s_t_id + "',  '" + s_r_id + "',  '" + s_pwd + "',  '" + s_station + "',  '" + s_status + "',  '" + s_rzsj + "',  '" + s_fh_id_list + "',  '" + s_dltflg + "',  '" + s_curtmobile + "',  '" + s_bankacc + "',  '" + s_fgs + "',  '" + s_old_id + "',  '" + s_Void_flag + "',  '" + s_Remark + "',  '" + s_CreateUser + "',  '" + s_CreateTime + "',  '" + s_ModifyUser + "',  '" + s_ModifyTime + "',  '" + s_VoidUser + "',  '" + s_VoidTime + "' )";
            }
            return C_ExcuteReader.ExcelDBsql(sql);
        }

        /// <summary>
        /// 
        /// </summary>
        public void SelectDr()
        {
            string sql;
            sql = "select  Id,  name,  NamePy,  IdentityNo,  tel,  no,  dsc,  t_id,  r_id,  pwd,  station,  status,  rzsj,  lzsj,  fh_id_list,  dltflg,  curtmobile,  bankacc,  fgs,  old_id,  Void_flag,  Remark,  CreateUser,  CreateTime,  ModifyUser,  ModifyTime,  VoidUser,  VoidTime   from zldc.T_s_tmp_allusr where Id= '" + s_Id + "'";
            SqlDataReader dr;
            DataBase db = new DataBase();
            dr = db.SelectRecord(sql);
            if (dr.Read())
            {
                s_Id = dr["Id"].ToString();
                s_name = dr["name"].ToString();
                s_NamePy = dr["NamePy"].ToString();
                s_IdentityNo = dr["IdentityNo"].ToString();
                s_tel = dr["tel"].ToString();
                s_no = dr["no"].ToString();
                s_dsc = dr["dsc"].ToString();
                s_t_id = dr["t_id"].ToString();
                s_r_id = dr["r_id"].ToString();
                s_pwd = dr["pwd"].ToString();
                s_station = dr["station"].ToString();
                s_status = dr["status"].ToString();
                s_rzsj = dr["rzsj"].ToString();
                s_lzsj = dr["lzsj"].ToString();
                s_fh_id_list = dr["fh_id_list"].ToString();
                s_dltflg = dr["dltflg"].ToString();
                s_curtmobile = dr["curtmobile"].ToString();
                s_bankacc = dr["bankacc"].ToString();
                s_fgs = dr["fgs"].ToString();
                s_old_id = dr["old_id"].ToString();
                s_Void_flag = dr["Void_flag"].ToString();
                s_Remark = dr["Remark"].ToString();
                s_CreateUser = dr["CreateUser"].ToString();
                s_CreateTime = dr["CreateTime"].ToString();
                s_ModifyUser = dr["ModifyUser"].ToString();
                s_ModifyTime = dr["ModifyTime"].ToString();
                s_VoidUser = dr["VoidUser"].ToString();
                s_VoidTime = dr["VoidTime"].ToString();
            }
            dr.Close();
            db.ConnectClose();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="DeptID"></param>
        public string GetDeptName(string DeptID)
        {
            string strSql = "Select * from t_s_allDept where id=" + DeptID;
            DataBase db = new DataBase();
            SqlDataReader dr = db.SelectRecord(strSql);
            string strName = string.Empty;
            if (dr.Read())
            {
                strName = dr["Name"].ToString();
            }
            dr.Close();
            db.ConnectClose();

            return strName;

        }
    }
}
